package it.basketstats.utility.DB;

import java.util.ArrayList;
import java.util.Date;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

// TODO [StatsDB] da implementare il resto dei metodi e da testare
public class StatsDB {

	StatsDBHelper helper;
	SQLiteDatabase db;

	public StatsDB(Context context) {
		helper = new StatsDBHelper(context);
	}

	/****** basic methods ******/

	/**
	 * Open the database in write mode
	 * 
	 * @throws SQLException
	 *             id the database cannot be opened
	 */
	public void open() throws SQLException {
		db = helper.getWritableDatabase();
	}

	public void close() {
		db.close();
	}

	/****** "teams" table methods ******/

	public long teamAdd(String name) throws SQLException {
		long ret;
		ContentValues t = new ContentValues();
		t.put(StatsDBContract.Teams.COLUMN_NAME, name);
		try {
			ret = db.insertOrThrow(StatsDBContract.Teams.TABLE_NAME, null, t);
		} catch (SQLException e) {
			throw (new SQLException());
		}
		return ret;
	}

	public long teamGetID(String name) {
		Cursor c;
		String[] columns = { StatsDBContract.Teams._ID };
		String[] selArgs = { name };
		c = db.query(StatsDBContract.Teams.TABLE_NAME, columns,
				StatsDBContract.Teams.COLUMN_NAME + " = ?", selArgs, null,
				null, null);

		if (c.moveToFirst())
			return c.getLong(0);
		else
			return -1;
	}

	public ArrayList<TeamEntity> teamGetAll() {
		ArrayList<TeamEntity> teams = new ArrayList<TeamEntity>();
		Cursor c;

		c = db.query(StatsDBContract.Teams.TABLE_NAME, null, null, null, null,
				null, null);

		if (c.getCount() > 0) {
			while (c.moveToNext()) {
				TeamEntity team = new TeamEntity();
				team.setId(c.getLong(0));
				team.setName(c.getString(1));
				teams.add(team);
			}
			return teams;
		} else {
			return null;
		}
	}

	public TeamEntity teamSearchByID(Long id) {
		TeamEntity team = new TeamEntity();
		Cursor c;
		String[] selArgs = { id.toString() };
		c = db.query(StatsDBContract.Teams.TABLE_NAME, null,
				StatsDBContract.Teams._ID + " = ?", selArgs, null, null, null);

		if (c.getCount() == 1) {
			c.moveToFirst();
			team.setId(c.getLong(0));
			team.setName(c.getString(1));

			return team;
		} else
			return null;
	}

	public TeamEntity teamSearchByName(String name) {
		long id = teamGetID(name);
		return teamSearchByID(id);
	}

	/**
	 * This method deletes all informations related the team: team entry itself,
	 * players, matches and stats.
	 * 
	 * @param id
	 *            id of the team
	 * 
	 * @exception SQLException
	 *                if none of the rows are deleted
	 */
	public void teamDelete(Long id) throws SQLException {
		int rowDeleted;
		String[] whereArgs = { id.toString() };
		rowDeleted = db.delete(StatsDBContract.Teams.TABLE_NAME,
				StatsDBContract.Teams._ID + " = ?", whereArgs);
		if (rowDeleted < 1)
			throw (new SQLException());

		return;
	}

	/***** "players" table methods ******/

	/*** player getters ***/

	public long playerGetID(Long teamID, Integer num) {
		Cursor c;
		String[] columns = { StatsDBContract.Players._ID };
		String[] selArgs = { teamID.toString(), num.toString() };
		c = db.query(StatsDBContract.Players.TABLE_NAME, columns,
				StatsDBContract.Players.COLUMN_TEAM + " = ?, AND "
						+ StatsDBContract.Players.COLUMN_NUMBER + " = ?",
				selArgs, null, null, null);

		if (c.moveToFirst())
			return c.getLong(0);
		else
			return -1;
	}

	public long playerGetTeam(Long id) {
		Cursor c;
		String[] columns = { StatsDBContract.Players.COLUMN_TEAM };
		String[] selArgs = { id.toString() };
		c = db.query(StatsDBContract.Players.TABLE_NAME, columns,
				StatsDBContract.Players._ID + " = ?", selArgs, null, null, null);

		if (c.moveToFirst())
			return c.getLong(0);
		else
			return -1;
	}

	public int playerGetNumber(Long id) {
		Cursor c;
		String[] columns = { StatsDBContract.Players.COLUMN_NUMBER };
		String[] selArgs = { id.toString() };
		c = db.query(StatsDBContract.Players.TABLE_NAME, columns,
				StatsDBContract.Players._ID + " = ?", selArgs, null, null, null);

		if (c.moveToFirst())
			return c.getInt(0);
		else
			return -1;
	}

	public String playerGetName(Long id) {
		Cursor c;
		String[] columns = { StatsDBContract.Players.COLUMN_NAME };
		String[] selArgs = { id.toString() };
		c = db.query(StatsDBContract.Players.TABLE_NAME, columns,
				StatsDBContract.Players._ID + " = ?", selArgs, null, null, null);

		if (c.moveToFirst())
			return c.getString(0);
		else
			return null;
	}

	public String playerGetSurname(Long id) {
		Cursor c;
		String[] columns = { StatsDBContract.Players.COLUMN_SURNAME };
		String[] selArgs = { id.toString() };
		c = db.query(StatsDBContract.Players.TABLE_NAME, columns,
				StatsDBContract.Players._ID + " = ?", selArgs, null, null, null);

		if (c.moveToFirst())
			return c.getString(0);
		else
			return null;
	}

	/*** end player getters ***/

	public boolean playerAdd(long team, int num, String name, String surname) {
		ContentValues t = new ContentValues();
		t.put(StatsDBContract.Players.COLUMN_TEAM, team);
		t.put(StatsDBContract.Players.COLUMN_NUMBER, num);
		t.put(StatsDBContract.Players.COLUMN_NAME, name);
		t.put(StatsDBContract.Players.COLUMN_SURNAME, surname);
		if (db.insert(StatsDBContract.Players.TABLE_NAME, null, t) != -1)
			return true;
		else
			return false;
	}

	public boolean playerAdd(long team, int num) {
		ContentValues t = new ContentValues();
		t.put(StatsDBContract.Players.COLUMN_TEAM, team);
		t.put(StatsDBContract.Players.COLUMN_NUMBER, num);
		if (db.insert(StatsDBContract.Players.TABLE_NAME, null, t) != -1)
			return true;
		else
			return false;
	}

	public boolean playerDelete(Long id) {
		String[] whereArgs = { id.toString() };
		if (db.delete(StatsDBContract.Players.TABLE_NAME,
				StatsDBContract.Players._ID + " = ?", whereArgs) > 1) {
			return true;
		} else {
			return false;
		}
	}

	public boolean playerUpdate(Long id, int num, String name, String surname) {
		ContentValues t = new ContentValues();
		String[] whereArg = { id.toString() };
		t.put(StatsDBContract.Players.COLUMN_NUMBER, num);
		t.put(StatsDBContract.Players.COLUMN_NAME, name);
		t.put(StatsDBContract.Players.COLUMN_SURNAME, surname);
		if (db.update(StatsDBContract.Players.TABLE_NAME, t,
				StatsDBContract.Players._ID + " = ?", whereArg) != -1)
			return true;
		else
			return false;
	}

	public boolean playerUpdate(Long id, int num) {
		ContentValues t = new ContentValues();
		String[] whereArg = { id.toString() };
		t.put(StatsDBContract.Players.COLUMN_NUMBER, num);
		if (db.update(StatsDBContract.Players.TABLE_NAME, t,
				StatsDBContract.Players._ID + " = ?", whereArg) != -1)
			return true;
		else
			return false;
	}

	public boolean playerUpdate(Long id, String name, String surname) {
		ContentValues t = new ContentValues();
		String[] whereArg = { id.toString() };
		t.put(StatsDBContract.Players.COLUMN_NAME, name);
		t.put(StatsDBContract.Players.COLUMN_SURNAME, surname);
		if (db.update(StatsDBContract.Players.TABLE_NAME, t,
				StatsDBContract.Players._ID + " = ?", whereArg) != -1)
			return true;
		else
			return false;
	}

	/****** "matches" table methods ******/

	/*** match getters ***/

	public long matchGetTeam(Long id) {
		Cursor c;
		String[] columns = { StatsDBContract.Matches.COLUMN_TEAM };
		String[] selArgs = { id.toString() };
		c = db.query(StatsDBContract.Matches.TABLE_NAME, columns,
				StatsDBContract.Matches._ID + " = ?", selArgs, null, null, null);

		if (c.moveToFirst())
			return c.getLong(0);
		else
			return -1;
	}

	public String matchGetOpponents(Long id) {
		Cursor c;
		String[] columns = { StatsDBContract.Matches.COLUMN_OPPONENTS };
		String[] selArgs = { id.toString() };
		c = db.query(StatsDBContract.Matches.TABLE_NAME, columns,
				StatsDBContract.Matches._ID + " = ?", selArgs, null, null, null);

		if (c.moveToFirst())
			return c.getString(0);
		else
			return null;
	}

	public Date matchGetDate(Long id) {
		Cursor c;
		String[] columns = { StatsDBContract.Matches.COLUMN_DATE };
		String[] selArgs = { id.toString() };
		c = db.query(StatsDBContract.Matches.TABLE_NAME, columns,
				StatsDBContract.Matches._ID + " = ?", selArgs, null, null, null);

		if (c.moveToFirst())
			return (new Date(c.getLong(0)));
		else
			return null;
	}

	public String matchGetResult(Long id) {
		Cursor c;
		String[] columns = { StatsDBContract.Matches.COLUMN_RESULT };
		String[] selArgs = { id.toString() };
		c = db.query(StatsDBContract.Matches.TABLE_NAME, columns,
				StatsDBContract.Matches._ID + " = ?", selArgs, null, null, null);

		if (c.moveToFirst())
			return c.getString(0);
		else
			return null;
	}

	/*** end match getters ***/
}
